package nl.saxion.act.dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import nl.saxion.act.db.DatabaseManager;
import nl.saxion.act.wt.Catagory;


public class CatagoryDAO {

	private String sqlGetCatagoryByID   	= "SELECT * FROM Catagory WHERE catagoryID = ? " ;
	private String sqlGetAllCatagory   	= "SELECT * FROM Catagory" ;
	private String sqlInsertCatagory   	= "INSERT INTO Catagory (catagory) VALUES (?) " ;
	
	private Connection        con      		= null ;
	private PreparedStatement psAllCatagory   = null ;
	private PreparedStatement psCatagory   	= null ;
	private PreparedStatement psInsertCatagory 	= null;

	private int countCatagorys          = 0;
	
	public CatagoryDAO(DatabaseManager myDb){
		con = myDb.getConnection();
		try{
			
			this.psCatagory   	= con.prepareStatement(sqlGetCatagoryByID) ;
			this.psInsertCatagory 	= con.prepareStatement(sqlInsertCatagory);
			this.psAllCatagory	= con.prepareStatement(sqlGetAllCatagory);
			
		    Statement stmt     = con.createStatement() ;
			ResultSet rs       = stmt.executeQuery("SELECT count(*) FROM Catagory") ;
			
			if (rs.next()) {
				countCatagorys = rs.getInt(1) ;
			}
			
			rs.close() ;
			stmt.close() ;
			
		} catch(SQLException se) {
			printSQLException(se) ;
		}
	}	
		
	public ArrayList<Catagory> getAllCatagory(){
		ArrayList<Catagory> Catagorys = new ArrayList<Catagory>();

		try{
		
			
			ResultSet rs = psAllCatagory.executeQuery();
			
			while (rs.next()) {
				int catagoryID      		= rs.getInt		("catagoryID");
				String name					= rs.getString	("catagory");
			
				Catagory Catagory 	= new Catagory(catagoryID, name);
				Catagorys.add(Catagory);
			}
			
			rs.close() ;
			
		} catch(SQLException se) {
			printSQLException(se) ;
			se.printStackTrace();
		}

		return Catagorys;
	}	
	
	public void insertCatagory(String name) {
		try {
			this.psInsertCatagory = con.prepareStatement(sqlInsertCatagory);
			psInsertCatagory.clearParameters();
			psInsertCatagory.setString(1, name);
			psInsertCatagory.executeUpdate();
			
		} catch (SQLException se) {
			printSQLException(se);
			se.printStackTrace();
		}

	}
		
	public int getCountCatagorys() {
		return countCatagorys;
	}
;
	private void printSQLException(SQLException se) {
//		while(se != null) {
//
//			System.out.print("SQLException: State:   " + se.getSQLState());
//			System.out.println("Severity: " + se.getErrorCode());
//			System.out.println(se.getMessage());			
//
//			se = se.getNextException();
//		}
		se.printStackTrace();
	}

}
